﻿using CommonTool;
using Dapper;
using DbAttributes;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace DbService
{
    public class DbHelper
    {

        static ConfigurationTool tool = new ConfigurationTool();
        static string ConnectionStr = tool.GetAppSettings("connectionStr");
        static IDbConnection connection = new SqlConnection(ConnectionStr);
        

        public static List<dynamic> Query(string sql)
        {
            return connection.Query<dynamic>(sql).ToList();
        }


        public static List<T> Query<T>(string sql)
        {
            return connection.Query<T>(sql).ToList();
        }

        public static T QueryFirst<T>(string sql)
        {
            return connection.QueryFirst<T>(sql);
        }

        public static void Add<T>(T model)
        {
            Type t = typeof(T);
            string TableName = GetTableName(t);
            PropertyInfo[] pps = t.GetProperties();
            StringBuilder sb = new StringBuilder("insert into"+ TableName + "(");
            StringBuilder sbColumns = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            foreach (PropertyInfo item in pps)
            {
                if (!IsNotMapper(item)&& !IsKey(item))
                {
                    sbColumns.Append(item.Name+",");
                    if (IsNoStr(item.PropertyType))
                    {
                        
                        if (item.GetValue(model)==null|| string.IsNullOrEmpty(item.GetValue(model).ToString()))
                        {
                            sbValues.Append("null,");
                        }
                        else
                        {
                            
                            if (IsBoolean(item.PropertyType))
                            {
                                bool bvalue = (bool)item.GetValue(model);
                                sbValues.Append((bvalue?1:0) + ",");
                            }
                            else
                            {
                                sbValues.Append(item.GetValue(model) + ",");
                            }
                            
                        }
                    }
                    else
                    {
                        sbValues.Append("'"+item.GetValue(model) + "',");
                    }
                    
                }
            }
            string sql = string.Format("insert into " + TableName + "({0}) values({1})",sbColumns.ToString().TrimEnd(','), sbValues.ToString().TrimEnd(','));
            connection.Execute(sql);
        }


        public static void Update<T>(T model)
        {
            Type t = typeof(T);
            string TableName = GetTableName(t);
            PropertyInfo[] pps = t.GetProperties();
            StringBuilder sb = new StringBuilder();
            string where = "";
            foreach (PropertyInfo item in pps)
            {
                if (!IsNotMapper(item))
                {
                    string column = item.Name;
                    string value = item.GetValue(model).ToString();
                    if (!IsKey(item))
                    {
                        if (IsNoStr(item.PropertyType))
                        {
                            if (IsBoolean(item.PropertyType))
                            {
                                bool bvalue = (bool)item.GetValue(model);
                                value = bvalue ? "1" : "0";
                            }
                            sb.Append(column + "=" + value + ",");
                        }
                        else
                        {
                            sb.Append(column + "='" + value + "',");
                        }
                    }
                    else
                    {
                        where = column + "=" + value;
                    }
                }
            }

            string sql = string.Format("update {0} set {1} where {2} ",TableName,sb.ToString().TrimEnd(','),where);
        }


        public static void Delete<Key, T>(Key id)
        {
            Type tp = typeof(T);
            PropertyInfo[] pps = tp.GetProperties();
            string KeyName = "";
            Key Value = id;
            foreach (PropertyInfo item in pps)
            {
                if (!IsNotMapper(item)&& IsKey(item))
                {
                    KeyName = item.Name;
                }
            }
            if (IsNoStr(typeof(Key)))
            {
                string sql = "delete " + GetTableName(tp) + " where " + KeyName + "=" + Value;
            }
            else
            {
                string sql = "delete " + GetTableName(tp) + " where " + KeyName + "='" + Value + "'";
            }
        }



        public static string CreateTable<T>()
        {
            Type t = typeof(T);
            StringBuilder columns = new StringBuilder();
            PropertyInfo[] prs = t.GetProperties();
            string keyName = "id";
            foreach (var item in prs)
            {
                columns.Append("[" + item.Name + "][" + CSharpTypeToSqlType(item.PropertyType) + "]");
                //这部分多个判断的地方都可以引入策略模式，来加强可维护性，后期新增
                if (item.PropertyType==typeof(System.String))
                {
                    object[] cols = item.GetCustomAttributes(typeof(ColumnAttribute), true);
                    if (cols.Length<=0)
                    {
                        columns.Append("(255)");
                    }
                    else
                    {
                        ColumnAttribute col = cols[0] as ColumnAttribute;
                        columns.Append("("+ col.ColumnLength + ")");
                    }
                }
                if (item.PropertyType == typeof(System.Decimal))
                {
                    object[] cols = item.GetCustomAttributes(typeof(ColumnAttribute), true);
                    if (cols.Length <= 0)
                    {
                        columns.Append("(2，4)");
                    }
                    else
                    {
                        ColumnAttribute col = cols[0] as ColumnAttribute;
                        columns.Append("(" + col.ColumnMinLength + ","+ col.ColumnMaxLength +")");
                    }
                }
                object[] keys = item.GetCustomAttributes(typeof(KeyAttribute), true);
                if (keys.Length>0)
                {
                    keyName = item.Name;
                    if (item.PropertyType== typeof(System.Int32)|| item.PropertyType == typeof(System.Int16) || item.PropertyType == typeof(System.Int64))
                    {
                        columns.Append(" IDENTITY(1, 1) ");
                    }
                }
                object[] nns = item.GetCustomAttributes(typeof(NotNullAttribute), true);
                if (nns.Length>0 || keys.Length >0)
                {
                    columns.Append(" NOT NULL, ");
                }
                else
                {
                    columns.Append(" NULL,");
                }
            }
            string TableName = GetTableName(t);
            string Pk = "PK_" + TableName + "_" + keyName + "1";
            string sql = "CREATE TABLE [dbo].["+ TableName + "]("+
                    columns.ToString().TrimEnd(',') +
                 "  CONSTRAINT["+ Pk + "] PRIMARY KEY CLUSTERED" +
                "(["+ keyName + "] ASC)" +
                "WITH(PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON[PRIMARY]" +
                ") ON[PRIMARY]";
            return sql;
        }



        #region 私有方法


        /// <summary>
        /// 是否为主键
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        private static bool IsKey(PropertyInfo item)
        {
            return IsAttribute(item, typeof(KeyAttribute));
        }


        /// <summary>
        /// 是否包含不映射特性
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        private static bool IsNotMapper(PropertyInfo item)
        {
            return IsAttribute(item, typeof(NotMapperAttribute));
        }

        /// <summary>
        /// 是否包含该特性
        /// </summary>
        /// <param name="item"></param>
        /// <param name="tp"></param>
        /// <returns></returns>
        private static bool IsAttribute(PropertyInfo item, Type tp)
        {
            var attr = item.GetCustomAttributes(tp, true);
            if (attr.Length <= 0)
                return false;
            return true;
        }


        /// <summary>
        /// 查询表名
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        private static string GetTableName(Type t)
        {
            string Name = t.Name;
            object[] os = t.GetCustomAttributes(typeof(TablNameAttribute), true);
            if (os.Length > 0)
            {
                TablNameAttribute ta = os[0] as TablNameAttribute;
                Name = ta.TableName;
            }
            return Name;
        }


        /// <summary>
        /// 是否为布尔类型
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        private static bool IsBoolean(Type t)
        {
            if (t == typeof(System.Boolean))
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 是否为非字符串
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        private static bool IsNoStr(Type t)
        {
            bool rs = (t == typeof(System.Int32)
                || t == typeof(System.Nullable<System.Int32>)
                || t == typeof(System.Int16)
                || t == typeof(System.Nullable<System.Int16>)
                || t == typeof(System.Int64)
                || t == typeof(System.Nullable<System.Int64>)
                || t == typeof(System.Boolean)
                || t == typeof(System.Nullable<System.Boolean>)
                || t == typeof(System.Double)
                || t == typeof(System.Nullable<System.Double>)
                || t == typeof(System.Decimal)
                || t == typeof(System.Nullable<System.Decimal>)
                        );
            return rs;
        }


        /// <summary>
        /// 类数据类型转SQL数据类型
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        private static string CSharpTypeToSqlType(Type t)
        {
            if (t== typeof(System.Int32))
            {
                return "int";
            }
            if (t == typeof(System.Int16))
            {
                return "int";
            }
            if (t == typeof(System.Int64))
            {
                return "int";
            }
            if (t == typeof(System.String))
            {
                return "varchar";
            }
            if (t == typeof(System.DateTime))
            {
                return "datetime";
            }
            if (t == typeof(System.Boolean))
            {
                return "bit";
            }
            if (t == typeof(System.Guid))
            {
                return "uniqueidentifier";
            }
            if (t == typeof(System.Decimal))
            {
                return "decimal";
            }
            return "text";
        }



        #endregion

    }

}
